﻿//====================================================================
// Create by Daniel
// file： YCUser.cs
// project ：YunCMS
// Build time：2012-09-06
// modify:
// description:
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using YunCMS.Model;
using YunCMS.IDAL;

namespace YunCMS.DAL
{
    /// <summary>
    /// Data Access Layer of dbo.YC_User.
    /// </summary>
    public partial class YCUserDAL : IYCUserDAL
    {
		#region 
		
		/// <summary>
		/// constractor
		/// </summary>
		public YCUserDAL()
		{
		}
		#endregion

        #region -----------Methods-----------
        public int Add(YCUser model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into YC_User(");
            sqlStr.Append("UserAccount,Password,Email,Telephone,Name,Deactive,UpdateTime,LoginTimes,Gender,LastLogin,OtherInfo,Dept,Role)");
            sqlStr.Append(" values (");
            sqlStr.Append("@UserAccount,@Password,@Email,@Telephone,@Name,@Deactive,getdate(),@LoginTimes,@Gender,@LastLogin,@OtherInfo,@Dept,@Role)");
            sqlStr.Append(";select @@IDENTITY");
            int res;

            res = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, false));
            return res;
        }
        /// <summary>
        /// Insert a model to Database in transaction.
        /// </summary>
        /// <param name="trans">SqlTransaction</param>
        /// <param name="model">YCUsernew model</param>
        /// <returns>The new item Id</returns>
        public int Add(SqlTransaction trans, YCUser model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into YC_User(");
            sqlStr.Append("UserAccount,Password,Email,Telephone,Name,Deactive,UpdateTime,LoginTimes,Gender,LastLogin,OtherInfo,Dept,Role)");
            sqlStr.Append(" values (");
            sqlStr.Append("@UserAccount,@Password,@Email,@Telephone,@Name,@Deactive,getdate(),@LoginTimes,@Gender,@LastLogin,@OtherInfo,@Dept,@Role)");
            int res;

            res = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, false));
            return res;
        }
        /// <summary>
        /// update a model to db
        /// </summary>
        /// <param name="model">YCUser</param>
        /// <returns>rows affected</returns>
        public int Update(YCUser model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update YC_User set ");
            sqlStr.Append("UserAccount=@UserAccount,Password=@Password,Email=@Email,Telephone=@Telephone,Name=@Name,Deactive=@Deactive,UpdateTime=getdate(),LoginTimes=@LoginTimes,Gender=@Gender,LastLogin=@LastLogin,OtherInfo=@OtherInfo,Dept=@Dept,Role=@Role");
            sqlStr.Append(" where ID=@ID");
            int res;

            res = SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, true));
            return res;
        }

        /// <summary>
        /// update a model to db in transaction
        /// </summary>
        /// <param name="trans">SqlTransaction</param>
        /// <param name="model">YCUser</param>
        /// <returns>rows affected</returns>
        public int Update(SqlTransaction trans, YCUser model)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update YC_User set ");
            sqlStr.Append("UserAccount=@UserAccount,Password=@Password,Email=@Email,Telephone=@Telephone,Name=@Name,Deactive=@Deactive,UpdateTime=getdate(),LoginTimes=@LoginTimes,Gender=@Gender,LastLogin=@LastLogin,OtherInfo=@OtherInfo,Dept=@Dept,Role=@Role");
            sqlStr.Append(" where ID=@ID");
            int res;

            res = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), PrepareSqlParams(model, true));
            return res;
        }

        public int UpdateOneField(int id, string fieldName, object value)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update YC_User set ");
            sqlStr.Append(fieldName + "=@fieldValue");
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.Int),
			                new SqlParameter("@fieldValue",value)
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), parameters);
        }

		
		/// <summary>
		/// delete a model from db
		/// </summary>
	    /// <param name="id">ID</param>
		/// <returns>rows affected</returns>
		public int Delete(int id)
		{
			StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete YC_User ");
            sqlStr.Append(" where ID=@ID");
			SqlParameter[] parameters={
							new SqlParameter("@ID",SqlDbType.Int),
			
			};
			parameters[0].Value=id;
			return SqlHelper.ExecuteNonQuery(CommandType.Text,sqlStr.ToString(),parameters);
		}
		
        /// <summary>
		/// delete a model from db in transaction
		/// </summary>        
        /// <param name="trans">SqlTransaction</param>
	    /// <param name="id">ID</param>
		/// <returns>rows affected</returns>
		public int Delete(SqlTransaction trans,int id)
		{
			StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete YC_User ");
            sqlStr.Append(" where ID=@ID");
			SqlParameter[] parameters={
							new SqlParameter("@ID",SqlDbType.Int),
			
			};
			parameters[0].Value=id;
			return SqlHelper.ExecuteNonQuery(trans,CommandType.Text,sqlStr.ToString(),parameters);
		}
		
        /// <summary>
		///Get YCUser Entity
		/// </summary>
		/// <param name="dr">dr</param>
		/// <returns>YCUser Entity</returns>
		public YCUser PopulateYCUserFromDr(IDataReader dr)
		{
			YCUser Obj = new YCUser();
			
				Obj.ID = Convert.ToInt32( dr["ID"]);
				Obj.UserAccount =  dr["UserAccount"].ToString();
                Obj.Email =  dr["Email"].ToString();
                Obj.Telephone =  dr["Telephone"].ToString();
                Obj.Name =  dr["Name"].ToString();
				Obj.Deactive = (bool) dr["Deactive"];
                if(( dr["UpdateTime"])==DBNull.Value){ Obj.UpdateTime = null; } else { Obj.UpdateTime = Convert.ToDateTime( dr["UpdateTime"]);}
                if(( dr["LoginTimes"])==DBNull.Value){ Obj.LoginTimes = null; } else { Obj.LoginTimes = Convert.ToInt32( dr["LoginTimes"]);}
                if(( dr["Gender"])==DBNull.Value){ Obj.Gender = null; } else { Obj.Gender = (bool) dr["Gender"];}
                if(( dr["LastLogin"])==DBNull.Value){ Obj.LastLogin = null; } else { Obj.LastLogin = Convert.ToDateTime( dr["LastLogin"]);}
                Obj.OtherInfo =  dr["OtherInfo"].ToString();
                Obj.Dept =  dr["Dept"].ToString();
                if(( dr["Role"])==DBNull.Value){ Obj.Role = null; } else { Obj.Role = Convert.ToInt32( dr["Role"]);}
			
			return Obj;
		}
		
		/// <summary>
		/// get a YCUser object 
		/// </summary>
		/// <param name=id>ID</param>
		/// <returns>YCUser object</returns>
		public YCUser GetYCUser (int id)
		{
			YCUser _obj=null;
			SqlParameter[] parameters={
			new SqlParameter("@ID",SqlDbType.Int)
			};
			parameters[0].Value=id;
			string sqlStr="select * from YC_User where ID=@ID";
			using(SqlDataReader dr=SqlHelper.ExecuteReader(CommandType.Text,sqlStr,parameters))
			{
				if(dr.Read())
				{
            		_obj = PopulateYCUserFromDr(dr);
				}
			}
			return _obj;
		}

        /// <summary>
        /// get a YCUser object 
        /// </summary>
        /// <param name=id>ID</param>
        /// <returns>YCUser object</returns>
        public YCUser GetYCUser(string userAccount)
        {
            YCUser _obj = null;
            SqlParameter[] parameters ={
			new SqlParameter("@UserAccount",SqlDbType.NVarChar)
			};
            parameters[0].Value = userAccount;
            string sqlStr = "select top 1 * from YC_User where UserAccount=@UserAccount";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr, parameters))
            {
                if (dr.Read())
                {
                    _obj = PopulateYCUserFromDr(dr);
                }
            }
            return _obj;
        }

		/// <summary>
		/// Get YCUsers List
		/// </summary>
		/// <returns>List</returns>
		public IList<YCUser> GetList(string userName)
		{
			IList< YCUser> Obj=new List< YCUser>();
            SqlParameter[] parameters ={
			    new SqlParameter("@UserAccount",SqlDbType.NVarChar)
			};
            parameters[0].Value = userName;
            string sqlStr = "select * from YC_User where UserAccount like @UserAccount+'%'";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr, parameters))
			{
				while(dr.Read())
				{
					Obj.Add(PopulateYCUserFromDr(dr));
				}
			}
			return Obj;
		}

		/// <summary>
        /// if exist a YCUser according by ID
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>true/false</returns>
        public bool IsExist(string userAccount, string password)
		{
			SqlParameter[] parameters={
                                      new SqlParameter("@UserAccount",SqlDbType.NVarChar),
							          new SqlParameter("@Password",SqlDbType.NVarChar)
                                  };
            parameters[0].Value = userAccount;
            parameters[1].Value = password;
            string sqlStr = "select Count(1) from YC_User where UserAccount=@UserAccount and Password=@Password";
            int a=Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text,sqlStr,parameters));
            if(a>0)
            {
                return true;
            }
            else
            {
                return false;
            }
		}

        /// <summary>
        /// if exist a YCUser according by ID
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>true/false</returns>
        public bool IsExist(int id)
        {
            SqlParameter[] parameters ={
                                      new SqlParameter("@ID",SqlDbType.Int)
                                  };
            parameters[0].Value = id;
            string sqlStr = "select Count(1) from YC_User where ID=@ID";
            int a = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sqlStr, parameters));
            if (a > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private SqlParameter[] PrepareSqlParams(YCUser model, bool isUpdate)
        {
            List<SqlParameter> parameters = new List<SqlParameter>() {							
							new SqlParameter("@UserAccount",SqlDbType.NVarChar),
							new SqlParameter("@Password",SqlDbType.NVarChar),
							new SqlParameter("@Email",SqlDbType.NVarChar),
							new SqlParameter("@Telephone",SqlDbType.NVarChar),
							new SqlParameter("@Name",SqlDbType.NVarChar),
							new SqlParameter("@Deactive",SqlDbType.Bit),
							new SqlParameter("@LoginTimes",SqlDbType.Int),
							new SqlParameter("@Gender",SqlDbType.Bit),
							new SqlParameter("@LastLogin",SqlDbType.DateTime),
							new SqlParameter("@OtherInfo",SqlDbType.NVarChar),
							new SqlParameter("@Dept",SqlDbType.NVarChar),
							new SqlParameter("@Role",SqlDbType.Int)
			};
            parameters[0].Value = model.UserAccount;
            parameters[1].Value = model.Password;
            if (model.Email != String.Empty)
                parameters[2].Value = model.Email;
            else
                parameters[2].Value = DBNull.Value;
            if (model.Telephone != String.Empty)
                parameters[3].Value = model.Telephone;
            else
                parameters[3].Value = DBNull.Value;
            if (model.Name != String.Empty)
                parameters[4].Value = model.Name;
            else
                parameters[4].Value = DBNull.Value;
            parameters[5].Value = model.Deactive;
            if (model.LoginTimes != null)
                parameters[6].Value = model.LoginTimes;
            else
                parameters[6].Value = DBNull.Value;
            if (model.Gender != null)
                parameters[7].Value = model.Gender;
            else
                parameters[7].Value = DBNull.Value;
            if (model.LastLogin != null)
                parameters[8].Value = model.LastLogin;
            else
                parameters[8].Value = DBNull.Value;
            if (model.OtherInfo != String.Empty)
                parameters[9].Value = model.OtherInfo;
            else
                parameters[9].Value = DBNull.Value;
            if (model.Dept != String.Empty)
                parameters[10].Value = model.Dept;
            else
                parameters[10].Value = DBNull.Value;
            if (model.Role != null)
                parameters[11].Value = model.Role;
            else
                parameters[11].Value = DBNull.Value;

            if (isUpdate)
            {
                SqlParameter param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = model.ID;
                parameters.Add(param);
            }

            return parameters.ToArray();
        }

        #endregion
		
    }
}




